﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace SindSaudeCamadas.Dados
{
    public class Albuns
    {
        Conex conexao = new Conex();
        public bool insereAlbuns(string strNome, string strData, string strCaminhoImagem)
        {
            bool insere = false;
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "INSERT INTO tbalbuns(STRNOME,STRDATA,PUBLICO,DATCRIACAO,STRCAMINHOIMAGEM)VALUES(@STRNOME,@STRDATA,@PUBLICO,@DATCRIACAO,@STRCAMINHOIMAGEM);";
                cmdSQL.Parameters.AddWithValue("@STRNOME", strNome);
                cmdSQL.Parameters.AddWithValue("@STRDATA", strData);
                cmdSQL.Parameters.AddWithValue("@PUBLICO", 0);
                cmdSQL.Parameters.AddWithValue("@DATCRIACAO", DateTime.Now);
                cmdSQL.Parameters.AddWithValue("@STRCAMINHOIMAGEM", strCaminhoImagem);

                cmdSQL.Connection.Open();
                cmdSQL.ExecuteNonQuery();
                insere = true;
            }
            catch (Exception)
            {
                 insere = false;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
            }            
            return insere;
        }
        public DataTable carregaAlbuns()
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "select *,cast(concat('Fotos.aspx?a=', id) as char) as url  from tbalbuns where publico = 0 order by datCriacao desc;";

                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public DataTable carregaAlbunsCarrocel()
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "select *,cast(concat('Fotos.aspx?a=', id) as char) as url  from tbalbuns where publico = 0 order by datCriacao desc Limit 4;";

                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public DataTable carregaAlbuns(int idAlbum)
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "select *,cast(concat('Fotos.aspx?a=', id) as char) as url  from tbalbuns where ID = @ID ;";
                cmdSQL.Parameters.AddWithValue("@ID", idAlbum);

                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public DataTable carregaFotos(int idAlbun)
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "select * from tbalbumfoto where intExcluido = 0 and idAlbum = @idAlbum;";
                cmdSQL.Parameters.AddWithValue("@idAlbum", idAlbun); 

                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public bool insereFotos(int idAlbum, string strCaminhoImg)
        {
            bool insere = false;
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "INSERT INTO tbfotos(IDALBUM,STRCAMINHOIMG,INTEXCLUIDO)VALUES(@IDALBUM,@STRCAMINHOIMG,@INTEXCLUIDO);";
                cmdSQL.Parameters.AddWithValue("@IDALBUM", idAlbum);
                cmdSQL.Parameters.AddWithValue("@STRCAMINHOIMG", strCaminhoImg);
                cmdSQL.Parameters.AddWithValue("@INTEXCLUIDO", 0);
                
                cmdSQL.Connection.Open();
                cmdSQL.ExecuteNonQuery();
                insere = true;
            }
            catch (Exception)
            {
                insere = false;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
            }
            return insere;
        }
        public bool deleteFotos(int id)
        {
            bool delete = false;
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "UPDATE tbfotos SET intExcluido = 1 WHERE ID = @ID;";
                cmdSQL.Parameters.AddWithValue("@ID", id);

                cmdSQL.Connection.Open();
                cmdSQL.ExecuteNonQuery();
                delete = true;
            }
            catch (Exception)
            {
                delete = false;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
            }
            return delete;
        }

        public bool deleteAlbuns(int id)
        {
            bool delete = false;
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "UPDATE tbalbuns SET publico = 1 WHERE ID = @ID;";
                cmdSQL.Parameters.AddWithValue("@ID", id);

                cmdSQL.Connection.Open();
                cmdSQL.ExecuteNonQuery();
                delete = true;
            }
            catch (Exception)
            {
                delete = false;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
            }
            return delete;
        }
    }
}
